<?php

//============================================================+
// File name   : tce_import_questions.php
// Begin       : 2006-03-12
// Last Update : 2023-11-30
//
// Description : Import questions from an XML file.
//
// Author: Nicola Asuni
//
// (c) Copyright:
//               Nicola Asuni
//               Tecnick.com LTD
//               www.tecnick.com
//               info@tecnick.com
//
// License:
//    Copyright (C) 2004-2025 Nicola Asuni - Tecnick.com LTD
//    See LICENSE.TXT file for more information.
//============================================================+

/**
 * @file
 * Import questions from an XML file to a selected subject.
 * @package com.tecnick.tcexam.admin
 * @author Nicola Asuni
 * @since 2006-03-12
 */



require_once('../config/tce_config.php');

$pagelevel = K_AUTH_ADMIN_IMPORT;
require_once('../../shared/code/tce_authorization.php');

$thispage_title = $l['t_question_importer'];
require_once('../code/tce_page_header.php');
require_once('../../shared/code/tce_functions_form.php');
require_once('../../shared/code/tce_functions_tcecode.php');
require_once('../../shared/code/tce_functions_auth_sql.php');

$type = ! isset($type) || empty($type) ? 1 : (int) $type;

if ((isset($menu_mode) && $menu_mode == 'upload') && $_FILES['userfile']['name']) {
    require_once('../code/tce_functions_upload.php');
    // upload file
    $uploadedfile = F_upload_file('userfile', K_PATH_CACHE);
    if ($uploadedfile !== false) {
        $qimp = false;
        switch ($type) {
            case 1: {
                // standard TCExam XML format
                require_once('../code/tce_class_import_xml.php');
                $qimp = new XMLQuestionImporter(K_PATH_CACHE . $uploadedfile);
                break;
            }
            case 2: {
                // standard TCExam TSV format
                $qimp = F_TSVQuestionImporter(K_PATH_CACHE . $uploadedfile);
                break;
            }
            case 3: {
                // Custom TCExam XML format
                require_once('../code/tce_import_custom.php');
                $qimp = new CustomQuestionImporter(K_PATH_CACHE . $uploadedfile);
                break;
            }
        }

        if ($qimp) {
            F_print_error('MESSAGE', $l['m_importing_complete']);
        }
    }
}

echo '<div class="container">' . K_NEWLINE;

echo '<div class="tceformbox">' . K_NEWLINE;
echo '<form action="' . $_SERVER['SCRIPT_NAME'] . '" method="post" enctype="multipart/form-data" id="form_importquestions">' . K_NEWLINE;

echo '<div class="row">' . K_NEWLINE;
echo '<span class="label">' . K_NEWLINE;
echo '<label for="userfile">' . $l['w_upload_file'] . '</label>' . K_NEWLINE;
echo '</span>' . K_NEWLINE;
echo '<span class="formw">' . K_NEWLINE;
echo '<input type="hidden" name="MAX_FILE_SIZE" value="' . K_MAX_UPLOAD_SIZE . '" />' . K_NEWLINE;
echo '<input type="file" name="userfile" id="userfile" size="20" title="' . $l['h_upload_file'] . '" />' . K_NEWLINE;
echo '</span>' . K_NEWLINE;
echo '&nbsp;' . K_NEWLINE;
echo '</div>' . K_NEWLINE;

echo '<div class="row">' . K_NEWLINE;
echo '<span class="label">&nbsp;</span>' . K_NEWLINE;
echo '<div class="formw">' . K_NEWLINE;
echo '<fieldset class="noborder">' . K_NEWLINE;

echo '<legend title="' . $l['w_type'] . '">' . $l['w_type'] . '</legend>' . K_NEWLINE;
echo '<input type="radio" name="type" id="type_xml" value="1" title="TCExam XML Format"';
if ($type == 1) {
    echo ' checked="checked"';
}

echo ' />';
echo '<label for="type_xml">TCExam XML</label><br />' . K_NEWLINE;

echo '<input type="radio" name="type" id="type_tsv" value="2" title="TCExam TSV Format"' . K_NEWLINE;
if ($type == 2) {
    echo ' checked="checked"';
}

echo ' />';
echo '<label for="type_tsv">TCExam TSV</label>' . K_NEWLINE;

$custom_import = K_ENABLE_CUSTOM_IMPORT;
if ($custom_import !== '') {
    echo '<input type="radio" name="type" id="type_custom" value="3" title="' . $custom_import . '"' . K_NEWLINE;
    if ($type == 3) {
        echo ' checked="checked"';
    }

    echo ' />';
    echo '<label for="type_custom">' . $custom_import . '</label>' . K_NEWLINE;
}

echo '</fieldset>' . K_NEWLINE;
echo '</div>' . K_NEWLINE;
echo '</div>' . K_NEWLINE;

echo '<div class="row">' . K_NEWLINE;
echo '<br />' . K_NEWLINE;

// show upload button
F_submit_button('upload', $l['w_upload'], $l['h_submit_file']);

echo '</div>' . K_NEWLINE;
echo F_getCSRFTokenField() . K_NEWLINE;
echo '</form>' . K_NEWLINE;
echo '</div>' . K_NEWLINE;

echo '<div class="pagehelp">' . $l['hp_import_xml_questions'] . '</div>' . K_NEWLINE;
echo '</div>' . K_NEWLINE;

require_once('../code/tce_page_footer.php');

// ---------------------------------------------------------------------

/**
 * Import questions from TSV file (tab delimited text).
 * The format of TSV is the same obtained by exporting data from TCExam interface.
 * @param $tsvfile (string) TSV (tab delimited text) file name
 * @return boolean TRUE in case of success, FALSE otherwise
 */
function F_TSVQuestionImporter($tsvfile)
{
    global $l, $db;
    require_once('../config/tce_config.php');
    require_once('../../shared/code/tce_functions_auth_sql.php');
    $qtype = [
        'S' => 1,
        'M' => 2,
        'T' => 3,
        'O' => 4,
    ];
    $tsvfp = fopen($tsvfile, 'r');
    if ($tsvfp === false) {
        return false;
    }

    $current_module_id = 0;
    $current_subject_id = 0;
    $current_question_id = 0;
    $current_answer_id = 0;
    $questionhash = [];
    // for each row
    while ($qdata = fgetcsv($tsvfp, 0, "\t", '"')) {
        if ($qdata === null) {
            continue;
        }

        // get user data into array
        switch ($qdata[0]) {
            case 'M': { // MODULE
                $current_module_id = 0;
                if (! isset($qdata[2]) || empty($qdata[2])) {
                    break;
                }

                $module_enabled = (int) $qdata[1];
                $module_name = F_escape_sql($db, F_tsv_to_text($qdata[2]), false);
                // check if this module already exist
                $sql = 'SELECT module_id
					FROM ' . K_TABLE_MODULES . '
					WHERE module_name=\'' . $module_name . '\'
					LIMIT 1';
                if ($r = F_db_query($sql, $db)) {
                    if ($m = F_db_fetch_array($r)) {
                        // get existing module ID
                        if (! F_isAuthorizedUser(K_TABLE_MODULES, 'module_id', $m['module_id'], 'module_user_id')) {
                            // unauthorized user
                            $current_module_id = 0;
                        } else {
                            $current_module_id = $m['module_id'];
                        }
                    } else {
                        // insert new module
                        $sql = 'INSERT INTO ' . K_TABLE_MODULES . ' (
							module_name,
							module_enabled,
							module_user_id
							) VALUES (
							\'' . $module_name . '\',
							\'' . $module_enabled . '\',
							\'' . $_SESSION['session_user_id'] . '\'
							)';
                        if (! $r = F_db_query($sql, $db)) {
                            F_display_db_error();
                        } else {
                            // get new module ID
                            $current_module_id = F_db_insert_id($db, K_TABLE_MODULES, 'module_id');
                        }
                    }
                } else {
                    F_display_db_error();
                }

                break;
            }
            case 'S': { // SUBJECT
                $current_subject_id = 0;
                if ($current_module_id === 0) {
                    return;
                }

                if (! isset($qdata[2]) || empty($qdata[2])) {
                    break;
                }

                $subject_enabled = (int) $qdata[1];
                $subject_name = F_escape_sql($db, F_tsv_to_text($qdata[2]), false);
                $subject_description = '';
                if (isset($qdata[3])) {
                    $subject_description = F_empty_to_null(F_tsv_to_text($qdata[3]));
                }

                // check if this subject already exist
                $sql = 'SELECT subject_id
					FROM ' . K_TABLE_SUBJECTS . '
					WHERE subject_name=\'' . $subject_name . '\'
						AND subject_module_id=' . $current_module_id . '
					LIMIT 1';
                if ($r = F_db_query($sql, $db)) {
                    if ($m = F_db_fetch_array($r)) {
                        // get existing subject ID
                        $current_subject_id = $m['subject_id'];
                    } else {
                        // insert new subject
                        $sql = 'INSERT INTO ' . K_TABLE_SUBJECTS . ' (
							subject_name,
							subject_description,
							subject_enabled,
							subject_user_id,
							subject_module_id
							) VALUES (
							\'' . $subject_name . '\',
							' . $subject_description . ',
							\'' . $subject_enabled . '\',
							\'' . $_SESSION['session_user_id'] . '\',
							' . $current_module_id . '
							)';
                        if (! $r = F_db_query($sql, $db)) {
                            F_display_db_error();
                        } else {
                            // get new subject ID
                            $current_subject_id = F_db_insert_id($db, K_TABLE_SUBJECTS, 'subject_id');
                        }
                    }
                } else {
                    F_display_db_error();
                }

                break;
            }
            case 'Q': { // QUESTION
                $current_question_id = 0;
                if ($current_module_id === 0 || $current_subject_id === 0) {
                    return;
                }

                if (! isset($qdata[5])) {
                    break;
                }

                $question_enabled = (int) $qdata[1];
                $question_description = F_escape_sql($db, F_tsv_to_text($qdata[2]), false);
                $question_explanation = F_empty_to_null(F_tsv_to_text($qdata[3]));
                $question_type = $qtype[$qdata[4]];
                $question_difficulty = (int) $qdata[5];
                $question_position = isset($qdata[6]) ? F_zero_to_null($qdata[6]) : F_zero_to_null(0);

                $question_timer = isset($qdata[7]) ? (int) $qdata[7] : 0;

                $question_fullscreen = isset($qdata[8]) ? (int) $qdata[8] : 0;

                $question_inline_answers = isset($qdata[9]) ? (int) $qdata[9] : 0;

                $question_auto_next = isset($qdata[10]) ? (int) $qdata[10] : 0;

                // check if this question already exist
                $sql = 'SELECT question_id
					FROM ' . K_TABLE_QUESTIONS . '
					WHERE ';
                if (K_DATABASE_TYPE == 'ORACLE') {
                    $sql .= "dbms_lob.instr(question_description,'" . $question_description . "',1,1)>0";
                } elseif (K_DATABASE_TYPE === 'MYSQL' && K_MYSQL_QA_BIN_UNIQUITY) {
                    $sql .= "question_description='" . $question_description . "' COLLATE utf8_bin";
                } else {
                    $sql .= "question_description='" . $question_description . "'";
                }

                $sql .= ' AND question_subject_id=' . $current_subject_id . ' LIMIT 1';
                if ($r = F_db_query($sql, $db)) {
                    if ($m = F_db_fetch_array($r)) {
                        // get existing question ID
                        $current_question_id = $m['question_id'];
                        continue 2;
                    }
                } else {
                    F_display_db_error();
                }

                if (K_DATABASE_TYPE === 'MYSQL') {
                    // this section is to avoid the problems on MySQL string comparison
                    $maxkey = 240;
                    $strkeylimit = min($maxkey, strlen($question_description));
                    $stop = $maxkey / 3;
                    while (in_array(md5(strtolower(substr($current_subject_id . $question_description, 0, $strkeylimit))), $questionhash) && $stop > 0) {
                        // a similar question was already imported, so we change it a little bit to avoid duplicate keys
                        $question_description = '_' . $question_description;
                        $strkeylimit = min($maxkey, ($strkeylimit + 1));
                        --$stop; // variable used to avoid infinite loop
                    }

                    if ($stop == 0) {
                        F_print_error('ERROR', 'Unable to get unique question ID');
                        return;
                    }
                }

                $sql = 'START TRANSACTION';
                if (! $r = F_db_query($sql, $db)) {
                    F_display_db_error();
                }

                // insert question
                $sql = 'INSERT INTO ' . K_TABLE_QUESTIONS . ' (
					question_subject_id,
					question_description,
					question_explanation,
					question_type,
					question_difficulty,
					question_enabled,
					question_position,
					question_timer,
					question_fullscreen,
					question_inline_answers,
					question_auto_next
					) VALUES (
					' . $current_subject_id . ',
					\'' . $question_description . '\',
					' . $question_explanation . ',
					\'' . $question_type . '\',
					\'' . $question_difficulty . '\',
					\'' . $question_enabled . '\',
					' . $question_position . ',
					\'' . $question_timer . '\',
					\'' . $question_fullscreen . '\',
					\'' . $question_inline_answers . '\',
					\'' . $question_auto_next . '\'
					)';
                if (! $r = F_db_query($sql, $db)) {
                    F_display_db_error(false);
                } else {
                    // get new question ID
                    $current_question_id = F_db_insert_id($db, K_TABLE_QUESTIONS, 'question_id');
                    if (K_DATABASE_TYPE === 'MYSQL') {
                        $questionhash[] = md5(strtolower(substr($current_subject_id . $question_description, 0, $strkeylimit)));
                    }
                }

                $sql = 'COMMIT';
                if (! $r = F_db_query($sql, $db)) {
                    F_display_db_error();
                }

                break;
            }
            case 'A': { // ANSWER
                $current_answer_id = 0;
                if ($current_module_id === 0 || $current_subject_id === 0 || $current_question_id === 0) {
                    return;
                }

                if (! isset($qdata[4])) {
                    break;
                }

                $answer_enabled = (int) $qdata[1];
                $answer_description = F_escape_sql($db, F_tsv_to_text($qdata[2]), false);
                $answer_explanation = F_empty_to_null(F_tsv_to_text($qdata[3]));
                $answer_isright = (int) $qdata[4];
                $answer_position = isset($qdata[5]) ? F_zero_to_null($qdata[5]) : F_zero_to_null(0);

                $answer_keyboard_key = isset($qdata[6]) ? F_empty_to_null(F_tsv_to_text($qdata[6])) : F_empty_to_null('');

                // check if this answer already exist
                $sql = 'SELECT answer_id
					FROM ' . K_TABLE_ANSWERS . '
					WHERE ';
                if (K_DATABASE_TYPE == 'ORACLE') {
                    $sql .= "dbms_lob.instr(answer_description, '" . $answer_description . "',1,1)>0";
                } elseif (K_DATABASE_TYPE === 'MYSQL' && K_MYSQL_QA_BIN_UNIQUITY) {
                    $sql .= "answer_description='" . $answer_description . "' COLLATE utf8_bin";
                } else {
                    $sql .= "answer_description='" . $answer_description . "'";
                }

                $sql .= ' AND answer_question_id=' . $current_question_id . ' LIMIT 1';
                if ($r = F_db_query($sql, $db)) {
                    if ($m = F_db_fetch_array($r)) {
                        // get existing subject ID
                        $current_answer_id = $m['answer_id'];
                    } else {
                        $sql = 'START TRANSACTION';
                        if (! $r = F_db_query($sql, $db)) {
                            F_display_db_error();
                        }

                        $sql = 'INSERT INTO ' . K_TABLE_ANSWERS . ' (
							answer_question_id,
							answer_description,
							answer_explanation,
							answer_isright,
							answer_enabled,
							answer_position,
							answer_keyboard_key
							) VALUES (
							' . $current_question_id . ',
							\'' . $answer_description . '\',
							' . $answer_explanation . ',
							\'' . $answer_isright . '\',
							\'' . $answer_enabled . '\',
							' . $answer_position . ',
							' . $answer_keyboard_key . '
							)';
                        if (! $r = F_db_query($sql, $db)) {
                            F_display_db_error(false);
                            F_db_query('ROLLBACK', $db);
                        } else {
                            // get new answer ID
                            $current_answer_id = F_db_insert_id($db, K_TABLE_ANSWERS, 'answer_id');
                        }

                        $sql = 'COMMIT';
                        if (! $r = F_db_query($sql, $db)) {
                            F_display_db_error();
                        }
                    }
                } else {
                    F_display_db_error();
                }

                break;
            }
        } // end of switch
    }

    // end of while
    return true;
}

//============================================================+
// END OF FILE
//============================================================+
